﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Com.LongER.Framework.Utils;
//using Com.LongER.Business.ZDH001.ZDH001DSTableAdapters;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using Com.LongER.Framework.Utils.LogUtils;

namespace Com.LongER.Business.ZDH001
{
    public class CZDH001 : ABussinessBase
    {
        private Log log = Log.GetInstance();
        /// <summary>
        /// 
        /// </summary>
        /// <param name="htParam"></param>
        /// <returns></returns>
        public ZDH001DS.M_CHEDataTable GetCheList(Dictionary<string, string> dicParam)
        {
            string strCHEPAI_NUM = dicParam["CHEPAI_NUM"];
            string strSHO = dicParam["SHO"];
            string strYUNSHU_XINGTAI = dicParam["YUNSHU_XINGTAI"];
            //string strYUNSHU_XINGZHI = dicParam["YUNSHU_XINGZHI"];

            string strBETWEEN_START = ((int.Parse(dicParam["PAGE_NO"]) - 1) * 5 + 1).ToString();
            string strBETWEEN_END = (int.Parse(dicParam["PAGE_NO"]) * 5).ToString();

            ////DB接続文字列取得
            var connectionString = ConnectionStringUtil.GetConnectionString();
            SqlConnection con = new SqlConnection(connectionString);
            con.Open();
            string cnnstr = string.Empty;

            #region sqlContent
            // C#コード自動作成
            StringBuilder objSqlContent = new StringBuilder();
            objSqlContent.Append(" SELECT                                                                          ");
            objSqlContent.Append("     *                                                                           ");
            objSqlContent.Append(" FROM                                                                            ");
            objSqlContent.Append("     (                                                                           ");
            objSqlContent.Append("         SELECT                                                                  ");
            objSqlContent.Append("             C.CHEPAI_NUM    AS CHEPAI_NUM                                       ");
            objSqlContent.Append("             ,ISNULL(C.SHO,'') AS SHO                                            ");
            objSqlContent.Append("             ,ISNULL(S.CODE1_HANZI,'') AS SHO_HANZI                              ");
            //objSqlContent.Append("             ,ISNULL(C.OWER_CD,'')  AS OWER_CD                                   ");
            objSqlContent.Append("             ,ISNULL(KF.KEHU_HANZI,'') AS KEHU_HANZI                             ");
            objSqlContent.Append("             ,ISNULL(KF.TEL_NUM1,'') AS KEHU_TELNUM1                             ");
            objSqlContent.Append("             ,ISNULL(KF.TEL_NUM2,'') AS KEHU_TELNUM2                             ");
            //objSqlContent.Append("             ,ISNULL(C.JIASHIYUAN_ZHU,'') AS JIASHIYUAN_ZHU                      ");
            objSqlContent.Append("             ,ISNULL(ST1.STAFF_HANZI,'') AS ZHUJIA_HANZI                         ");
            objSqlContent.Append("             ,ISNULL(ST1.MOBIL1,'') AS ZHUJIA_MOBIL1                             ");
            //objSqlContent.Append("             ,ISNULL(ST1.MOBIL2,'') AS ZHUJIA_MOBIL2                             ");
            //objSqlContent.Append("             ,ISNULL(C.JIASHIYUAN_FU,'')  AS JIASHIYUAN_FU                       ");
            objSqlContent.Append("             ,ISNULL(ST2.STAFF_HANZI,'') AS FUJIA_HANZI                          ");
            objSqlContent.Append("             ,ISNULL(ST2.MOBIL1,'') AS FUJIA_MOBIL1                              ");
            //objSqlContent.Append("             ,ISNULL(ST2.MOBIL2,'') AS FUJIA_MOBIL2                              ");
            objSqlContent.Append("             ,ISNULL(C.YUNSHU_XINGTAI,'') AS YUNSHU_XINGTAI                      ");
            objSqlContent.Append("             ,ISNULL(YSXT.CODE1_HANZI,'') AS YSXT_HANZI                          ");
            objSqlContent.Append("             ,ISNULL(C.YUNSHU_XINGZHI,'') AS YUNSHU_XINGZHI                      ");
            objSqlContent.Append("             ,ISNULL(YSXZ.CODE1_HANZI,'') AS YSXZ_HANZI                          ");
            objSqlContent.Append("             ,ISNULL(C.CHELIANG_YUNYIN_ZHUANGTAI,'')  AS CHELIANG_YUNYIN_ZHUANGTAI  ");
            objSqlContent.Append("             ,ISNULL(YYZT.CODE1_HANZI,'') AS YYZT_HANZI                          ");
            objSqlContent.Append("             ,ISNULL(C.ZHUANGZAI_ZHONGLIANG,0)  AS  ZHUANGZAI_ZHONGLIANG         ");
            objSqlContent.Append("             ,ISNULL(C.CHELIANG_XINGTAI,'')  AS  CHELIANG_XINGTAI                ");
            objSqlContent.Append("             ,ISNULL(CLXT.CODE1_HANZI,'') AS CLXT_HANZI                          ");
            objSqlContent.Append("             ,ISNULL(C.CHE_CHANG, 0) AS  CHE_CHANG                               ");
            objSqlContent.Append("             ,ISNULL(C.CHE_KUAN, 0) AS CHE_KUAN                                  ");
            objSqlContent.Append("             ,ISNULL(C.BUY_DATE,'') AS BUY_DATE                                  ");
            objSqlContent.Append("             ,C.USED_YEARS  AS  USED_YEARS                                       ");
            objSqlContent.Append("             ,ISNULL(C.CHK_YOUXIAO_DATE,'')  AS CHK_YOUXIAO_DATE                 ");
            objSqlContent.Append("             ,ISNULL(C.CHK_ZUIJIN_DATE,'')  AS CHK_ZUIJIN_DATE                   ");
            objSqlContent.Append("             ,ISNULL(C.BEIZHU,'')  AS BEIZHU                                     ");
            objSqlContent.Append("             ,rn = ROW_NUMBER                                                    ");
            objSqlContent.Append("             (                                                                   ");
            objSqlContent.Append("             )                                                                   ");
            objSqlContent.Append("             OVER                                                                ");
            objSqlContent.Append("             (                                                                   ");
            objSqlContent.Append("             ORDER BY                                                            ");
            objSqlContent.Append("                 C.CHEPAI_NUM ASC                                                ");
            objSqlContent.Append("             )                                                                   ");
            objSqlContent.Append("         FROM                                                                    ");
            objSqlContent.Append("             M_CHELIANG C                                                        ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_KEHU KF                                                           ");
            objSqlContent.Append("             ON   C.OWER_CD = KF.KEHU_CD                                         ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_STAFF ST1                                                         ");
            objSqlContent.Append("             ON   C.JIASHIYUAN_ZHU = ST1.STAFF_ID                                ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_STAFF ST2                                                         ");
            objSqlContent.Append("             ON   C.JIASHIYUAN_FU = ST2.STAFF_ID                                 ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_TONGYONG S                                                        ");
            objSqlContent.Append("             ON  C.SHO = S.KEY1                                                  ");
            objSqlContent.Append("             AND S.CODE_CD = 'SSQF'                                              ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_TONGYONG YSXT                                                     ");
            objSqlContent.Append("             ON  C.YUNSHU_XINGTAI = YSXT.KEY1                                    ");
            objSqlContent.Append("             AND YSXT.CODE_CD = 'YSXT'                                           ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_TONGYONG YSXZ                                                     ");
            objSqlContent.Append("             ON  C.YUNSHU_XINGZHI = YSXZ.KEY1                                    ");
            objSqlContent.Append("             AND YSXZ.CODE_CD = 'YSXZ'                                           ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_TONGYONG YYZT                                                     ");
            objSqlContent.Append("             ON  C.SHO = YYZT.KEY1                                               ");
            objSqlContent.Append("             AND YYZT.CODE_CD = 'YYZT'                                           ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_TONGYONG CLXT                                                     ");
            objSqlContent.Append("             ON  C.SHO = CLXT.KEY1                                               ");
            objSqlContent.Append("             AND CLXT.CODE_CD = 'CLXT'                                           ");
            objSqlContent.Append("         WHERE                                                                   ");
            objSqlContent.Append("                 1 = 1                                                           ");
            if (!String.IsNullOrWhiteSpace(strCHEPAI_NUM))
                objSqlContent.Append("             AND C.CHEPAI_NUM = @CHEPAI_NUM                                  ");
            if (!String.IsNullOrWhiteSpace(strSHO))
                objSqlContent.Append("             AND C.SHO = @SHO                                                ");
            if (!String.IsNullOrWhiteSpace(strYUNSHU_XINGTAI))
                objSqlContent.Append("             AND C.YUNSHU_XINGTAI = @YUNSHU_XINGTAI                          ");
            //if (!String.IsNullOrWhiteSpace(strYUNSHU_XINGZHI))
            //    objSqlContent.Append("             AND C.YUNSHU_XINGZHI = @YUNSHU_XINGZHI                        ");
            objSqlContent.Append("     )                                                                           ");
            objSqlContent.Append("     A                                                                           ");
            objSqlContent.Append(" WHERE                                                                           ");
            objSqlContent.Append("         A.rn BETWEEN @BETWEEN_START AND @BETWEEN_END;                           ");
            #endregion

            cnnstr = objSqlContent.ToString();

            log.Debug("CZDH001:" + cnnstr);

            SqlCommand cmd = new SqlCommand(cnnstr, con);
            cmd.CommandText = cnnstr;
            cmd.CommandType = System.Data.CommandType.Text;
            //车牌号
            if (!String.IsNullOrWhiteSpace(strCHEPAI_NUM))
                cmd.Parameters.Add(getSqlParameter("CHEPAI_NUM", SqlDbType.Char, strCHEPAI_NUM));
            //所属区分
            if (!String.IsNullOrWhiteSpace(strSHO))
                cmd.Parameters.Add(getSqlParameter("SHO", SqlDbType.Char, strSHO));
            //运输形态
            if (!String.IsNullOrWhiteSpace(strYUNSHU_XINGTAI))
                cmd.Parameters.Add(getSqlParameter("YUNSHU_XINGTAI", SqlDbType.Char, strYUNSHU_XINGTAI));
            //运输性质
            //if (!String.IsNullOrWhiteSpace(strYUNSHU_XINGZHI))
            //    cmd.Parameters.Add(getSqlParameter("YUNSHU_XINGZHI", SqlDbType.Char, strYUNSHU_XINGZHI));
            //
            if (!String.IsNullOrWhiteSpace(strBETWEEN_START))
                cmd.Parameters.Add(getSqlParameter("BETWEEN_START", SqlDbType.Int, int.Parse(strBETWEEN_START)));
            //
            if (!String.IsNullOrWhiteSpace(strBETWEEN_END))
                cmd.Parameters.Add(getSqlParameter("BETWEEN_END", SqlDbType.Int, int.Parse(strBETWEEN_END)));

            SqlDataReader odr = cmd.ExecuteReader();

            ZDH001DS.M_CHEDataTable tb = new ZDH001DS.M_CHEDataTable();
            if (odr.HasRows)
            {
                // データ有り
                while (odr.Read())
                {
                    ZDH001DS.M_CHERow dr = tb.NewM_CHERow();
                    //row 1
                    dr.CHEPAI_NUM = odr["CHEPAI_NUM"].ToString();
                    dr.SHO_HANZI = odr["SHO_HANZI"].ToString();//所属区分汉字
                    dr.YSXT_HANZI = odr["YSXT_HANZI"].ToString();  //运输形态汉字
                    dr.YSXZ_HANZI = odr["YSXZ_HANZI"].ToString();//运输性质汉字
                    dr.YYZT_HANZI = odr["YYZT_HANZI"].ToString();//车辆运营状态汉字
                    dr.CHE_CHANG = odr["CHE_CHANG"].ToString();//车长
                    dr.CHE_KUAN = odr["CHE_KUAN"].ToString();//车宽
                    dr.ZHUANGZAI_ZHONGLIANG = odr["ZHUANGZAI_ZHONGLIANG"].ToString();//可承载重量
                    dr.USED_YEARS = odr["USED_YEARS"].ToString(); //使用年数
                    dr.CHK_YOUXIAO_DATE = odr["CHK_YOUXIAO_DATE"].ToString();//年检有效日期
                    dr.KEHU_HANZI = odr["KEHU_HANZI"].ToString();  //车主汉字
                    dr.KEHU_TELNUM1 = odr["KEHU_TELNUM1"].ToString();//联系电话1
                    dr.KEHU_TELNUM2 = odr["KEHU_TELNUM2"].ToString();//联系电话2
                    dr.ZHUJIA_HANZI = odr["ZHUJIA_HANZI"].ToString();//主驾驶员汉字
                    dr.ZHUJIA_MOBIL1 = odr["ZHUJIA_MOBIL1"].ToString();//主驾联系电话
                    dr.FUJIA_HANZI = odr["FUJIA_HANZI"].ToString();//副驾驶员汉字
                    dr.FUJIA_MOBIL1 = odr["FUJIA_MOBIL1"].ToString(); //副驾联系电话

                    tb.AddM_CHERow(dr);
                }
            }

            con.Close();
            return tb;
        }


    }
}